<?php

class VolumesController extends Controller
{
    var $year;
	var $week_no;
	
	public function actionYearweek()
	{
		$this->logAction();
		
		$this->year = date("Y");
	    $this->week_no = date("W");
		
		if ( isset($_POST['week_no']))
		{
			$this->year = $_POST['year'];
			$this->week_no = $_POST['week_no'];
			#step 1
			$this->getPersonalBV();			
			#step 2
			$this->getLeftRightBV();
			#step 3
			$this->getMatchBV();
			
			//$this->render('admin');
		} 
		else
		  $this->render('yearweek');
	}
	
	public function getPersonalBV () 
	{
		$connection = Yii::app()->db;

		$year = $this->year;
		$week_no = $this->week_no;
		
		$sql = "SELECT a.member_id, SUM(a.quantity*b.bv_points) as points
		               FROM gn_sales AS a 
		          LEFT JOIN gn_products AS b 
		                 ON (a.product_id=b.product_id)
		              WHERE (WEEK(a.dt_sale)=$week_no AND YEAR(a.dt_sale)=$year) 
		           GROUP BY a.member_id";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data = $dataReader->read() ) 
	    {
	    	//print_r($data);
			
	    	$sql= "";
	        $payout_id = $this->get_gnPayoutsID($year, $week_no, $data['member_id']);
	    	
	    	if ( $payout_id > 0 ) 
			{
			   #update
			   $bv_personal = $data['points'];
			   if ( $bv_personal < 1 )
			     $bv_personal=0;
			   
			   $sql = "UPDATE gn_payouts 
			              SET bv_personal=$bv_personal
			            WHERE payout_id=$payout_id";	
			}
			else 
			{
			   #insert	
			   $member_id = $data['member_id'];
			   $bv_personal = $data['points'];
			   $sql = "INSERT INTO gn_payouts 
			                   SET member_id=$member_id,
			                       bv_personal=$bv_personal,
			                       year=$year,
			                       week_no=$week_no,
			                       status='pending'";
			}
			
			$connection->createCommand($sql)->query();
	    }	  
	}
	
	public function getLeftRightBV () {
		$connection = Yii::app()->db;
		
		$year = $this->year;
		$week_no = $this->week_no;
		
		#get only active members status=1
		$sql = "SELECT a.member_id  
		               FROM gn_members AS a
		          LEFT JOIN gn_payouts AS b
		                 ON (a.member_id=b.member_id)
		              WHERE b.week_no=$week_no AND b.year=$year
		                AND a.status_id=1";
				   
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data = $dataReader->read() ) 
	    {
	       //print_r($data);
	       $member_id = $data['member_id']; 
	       $child = $this->getChildren($member_id, $year, $week_no);
		   
		   //print_r ($child);
		   
	       $bv_left = 0;
	       $bv_right = 0; 
	       
	       if ( isset($child['left']) )
		   {
		   	 $bv_left = $child['left']['bv_personal'];
	         $this->getDownlineBV($child['left']['member_id'], 1, $bv_left );
	       } 
	         
		   
		   if ( isset($child['right']) )
	       {
	       	  $bv_right = $child['right']['bv_personal'];
	       	  $this->getDownlineBV($child['right']['member_id'], 1, $bv_right );
	       }  
	         
	       $payout_id = $this->get_gnPayoutsID($year, $week_no, $data['member_id']);
	       
	       $sql = "UPDATE gn_payouts 
	                  SET bv_left=$bv_left, bv_right=$bv_right 
	                WHERE payout_id=$payout_id";
		   
		   $connection->createCommand($sql)->query();
		}
	}

    public function getMatchBV()
	{
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no;
		
		#get only active members status=1
		$sql = "SELECT payout_id, bv_left, bv_right  
		               FROM gn_payouts 
		              WHERE week_no=$week_no AND year=$year";
				   
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data=$dataReader->read() ) {
			$payout_id = $data['payout_id'];	
			$bv_matched = $this->computeMatchedBV($data['bv_left'], $data['bv_right']);
			$payout = $this->getPayout($bv_matched);
			$bv_matched_per_side = $bv_matched/2;
			$bv_left_carryover = $this->computeCarryOver( $data['bv_left'], $bv_matched_per_side );
			$bv_right_carryover = $this->computeCarryOver( $data['bv_right'], $bv_matched_per_side );
			
			$sql = "UPDATE gn_payouts 
			           SET bv_match=$bv_matched,  
			               payout=$payout,
			               bv_left_carryover=$bv_left_carryover,
			               bv_right_carryover=$bv_right_carryover       
			        WHERE payout_id=$payout_id";
			
			$connection->createCommand($sql)->query();
	    }
   }

   public function computeCarryOver( $side_points, $bv_limit )
   {
   	  if ( $bv_limit <= 0)
	    return $side_points;
	  
   	  $carryover = 0;  
   	  
   	  if ( $side_points >= $bv_limit )
	    $carryover = $side_points - $bv_limit;
	    
	  return $carryover;
   }
   
	
   public function computeMatchedBV($bv_left, $bv_right) 
   {
		$raw_match = 0;
		
		if ( ($bv_left==0) || ($bv_right==0) )
		  $raw_match = 0; #no match if one side is empty
		else if ( $bv_left > $bv_right )
		  $raw_match = $bv_left - $bv_right;
		else if ( $bv_right > $bv_left )
		  $raw_match = $bv_right - $bv_left;
		else 
		  $raw_match = $bv_left; #left and right are equal
		  
		return $raw_match * 2;	
	}
	
	public function getPayout( $bv_matched )
	{
		$payout = $bv_matched * 100; 	
		return $payout;
	}
	
	
	
	public function getChildren($member_id=0)
	{
		$connection = Yii::app()->db;
        $year = $this->year;
		$week_no = $this->week_no;
		/*
		$week_no = $_POST['week_no'];
		$year = $_POST['year'];
		$sql = "SELECT a.member_id, a.position, b.bv_personal
		               FROM gn_members AS a 
		          LEFT OUTER JOIN gn_payouts AS b
		               ON (a.member_id=b.member_id)
		               WHERE a.sponsor_id=$member_id
		                AND ((b.year=$year AND b.week_no=$week_no) OR (ISNULL(b.year) AND ISNULL(b.week_no)))
		         ";*/
				 
		
		$sql = "SELECT member_id, position, status_id
		               FROM gn_members 
		               WHERE sponsor_id=$member_id";
		               
		//echo "<hr>$sql<br>";			  
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
		
		$child = array();
		while ( $data = $dataReader->read() ) 
	    {
	       if ( $data['status_id']==1)	
	          $data['bv_personal'] = $this->getMemberPersonalBV($data['member_id']);
		   else 
			  $data['bv_personal'] = 0;    
		   
	       $child[$data['position']] = $data;
		}
		
		//print_r ($child);
		return $child;
	}
	
	public function getMemberPersonalBV($member_id=0)
	{
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no;
		
		$sql = "SELECT bv_personal 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		           AND week_no=$week_no
		           AND year=$year";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_personal']) )
	       return $data['bv_personal'];
        else
           return 0;
	}
	
	
	public function get_gnPayoutsID( $year, $week_no, $member_id )
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT payout_id  
		               FROM gn_payouts
		              WHERE week_no=$week_no 
		                AND year=$year
                        AND member_id=$member_id		           
		";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
		$data = $dataReader->read();
		
		if ( isset($data['payout_id']) )
		  return $data['payout_id'];
        else
          return 0;
	}
	
	public function getDownlineBV($member_id, $level, &$total_points) 
	{
		if ($level==10) return;
		$connection = Yii::app()->db;
		$year = $this->year;
		$week_no = $this->week_no;
		
		/*
		$sql = "SELECT a.member_id, b.bv_personal
		                      FROM gn_members AS a
		           LEFT OUTER JOIN gn_payouts AS b
		                        ON (a.member_id=b.member_id)
		                     WHERE a.sponsor_id=$member_id
		                       AND ((b.year=$year AND b.week_no=$week_no) OR (ISNULL(b.year) AND ISNULL(b.week_no)))
		                       
		";
		 * */
		
		$sql = "SELECT member_id, status_id  
		              FROM gn_members 
		                     WHERE sponsor_id=$member_id		                       
		";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
		
		while ( $data = $dataReader->read() ) 
		{
			if ( $data['status_id']==1)	
	          $data['bv_personal'] = $this->getMemberPersonalBV($data['member_id']);
		   else 
			  $data['bv_personal'] = 0;    
		   
			$total_points = $total_points + $data['bv_personal'];
			$this->getDownlineBV($data['member_id'], $level++, $total_points );
		}
	}
   
    public function array_implode( $glue, $separator, $array ) {
		if ( ! is_array( $array ) ) return $array;
		$string = array();
		foreach ( $array as $key => $val ) {
			if ( is_array( $val ) )
				$val = implode( ',', $val );
			$string[] = "{$key}{$glue}{$val}";	   
		}
		
		return implode( $separator, $string );
	}
	
    public function logAction()
	{
		$datetime = date("Y-m-d H:i:s");
		$user = Yii::app()->user->id;
		$action =  $this->id . '/' . $this->action->id;
		$data   = $this->array_implode( "=", "::", $_GET );
		$data  .= $this->array_implode( "=", "::", $_POST );
		
		$sql = "INSERT INTO gn_audit_trail SET
		               datetime='$datetime', 
					   user='$user',
					   action='$action',
					   data='$data'";
					   
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
	}
	
	public function renderResults() {
		
	}

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
}